﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Data.SqlClient;
using ERP_RORZE_SYSTEM;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Business
{
    public partial class panel_StructureCalculation : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        private SqlConnection masterConnection;        
        
        public panel_StructureCalculation()
        {
            InitializeComponent();
            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.masterConnection = new SqlConnection(mySetting.DB2ConnectionString);             

            LoadRobotNo();
        }

        ////////////////////////////////////
        /// Get Robot List
        ////////////////////////////////////
        private void LoadRobotNo()
        {   
            try
            {
                masterConnection.Open();
                string sql = "SELECT * FROM OPENQUERY(AS400_MPFLIB, 'SELECT STOHBN,STBHBN,STKOSU FROM MPST00')";
                SqlCommand cmd_sql = new SqlCommand(sql, masterConnection);                
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["STOHBN"].ToString().Trim();
                    item.Value = dataReader["STOHBN"].ToString().Trim();
                    this.robotNo.Properties.Items.Add(item);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                masterConnection.Close();
            }
            catch (Exception ex)
            {
                if (masterConnection.State == ConnectionState.Open)
                {
                    masterConnection.Close();
                }
                //MessageBox.Show("Cannot get Robot list");
                MessageBox.Show("Get Robot List: " + ex.Message);
            } 
        }

        //////////////////////////////////////////////
        /// Get Structure From Kosei
        //////////////////////////////////////////////
        private DataTable RobotStructure(string robotno)
        {
            DataTable StructureTable = new DataTable();
            StructureTable.Columns.Add("RobotNo", typeof(string));
            StructureTable.Columns.Add("PartNo", typeof(string));
            StructureTable.Columns.Add("Quantity", typeof(float));
            StructureTable.Columns.Add("PType", typeof(string));
            DataTable currentTable = new DataTable();
            currentTable.Columns.Add("RobotNo", typeof(string));
            currentTable.Columns.Add("PartNo", typeof(string));
            currentTable.Columns.Add("Quantity", typeof(float));
            currentTable.Columns.Add("PType", typeof(string));
            DataTable nextTable = new DataTable();
            nextTable.Columns.Add("RobotNo", typeof(string));
            nextTable.Columns.Add("PartNo", typeof(string));
            nextTable.Columns.Add("Quantity", typeof(float));
            nextTable.Columns.Add("PType", typeof(string));
            DataTable resultTable = new DataTable();
            resultTable.Columns.Add("RobotNo", typeof(string));
            resultTable.Columns.Add("PartNo", typeof(string));
            resultTable.Columns.Add("Quantity", typeof(float));
            resultTable.Columns.Add("PType", typeof(string));

            if (!string.IsNullOrEmpty(robotno))
            {   
                try
                {
                    masterConnection.Open();
                    SqlCommand cmd_sql = masterConnection.CreateCommand();
                    SqlDataReader dataReader;

                    // Calculate for level 1
                    cmd_sql.CommandText = "SELECT * FROM OPENQUERY(AS400_MPFLIB, 'SELECT MPST00.STOHBN, MPST00.STBHBN,"
                        + " MPST00.STKOSU, MPIT00.ITHNTP"
                        + " FROM MPST00 LEFT JOIN MPIT00 ON MPST00.STBHBN=MPIT00.ITHNBN"
                        + " WHERE MPST00.STOHBN=''" + robotno + "''')";                    
                    dataReader = cmd_sql.ExecuteReader();
                    while (dataReader.Read())
                    {
                        DataRow new_row = currentTable.NewRow();
                        new_row["RobotNo"] = dataReader["STOHBN"].ToString().Trim();
                        new_row["PartNo"] = dataReader["STBHBN"].ToString().Trim();
                        float quantity = 0;
                        quantity = float.Parse(dataReader["STKOSU"].ToString().Trim());
                        new_row["Quantity"] = quantity;
                        new_row["PType"] = dataReader["ITHNTP"].ToString();
                        currentTable.Rows.Add(new_row);
                    }
                    dataReader.Close();
                    cmd_sql.Dispose();


                    // Calculate for all level
                    while (currentTable.Rows.Count > 0)
                    {
                        cmd_sql = masterConnection.CreateCommand();
                        foreach (DataRow row in currentTable.Rows)
                        {
                            if (row["RobotNo"].ToString() != row["PartNo"].ToString())
                            {
                                try
                                {
                                    cmd_sql.CommandText = "SELECT * FROM OPENQUERY(AS400_MPFLIB, 'SELECT MPST00.STOHBN, MPST00.STBHBN,"
                                        + " MPST00.STKOSU*" + row["Quantity"].ToString().Trim() + " AS STKOSU, MPIT00.ITHNTP"
                                        + " FROM MPST00 LEFT JOIN MPIT00 ON MPST00.STBHBN=MPIT00.ITHNBN"
                                        + " WHERE MPST00.STOHBN=''" + row["PartNo"].ToString() + "''')";
                                    dataReader = cmd_sql.ExecuteReader();
                                    int i = 0;
                                    while (dataReader.Read())
                                    {
                                        if (dataReader["STOHBN"] != DBNull.Value)
                                        {
                                            i++;
                                            DataRow new_row = nextTable.NewRow();
                                            new_row["RobotNo"] = dataReader["STOHBN"].ToString().Trim();
                                            new_row["PartNo"] = dataReader["STBHBN"].ToString().Trim();
                                            float quantity = 0;
                                            quantity = float.Parse(dataReader["STKOSU"].ToString().Trim());
                                            new_row["Quantity"] = quantity;
                                            new_row["PType"] = dataReader["ITHNTP"].ToString();
                                            nextTable.Rows.Add(new_row);
                                        }
                                    }
                                    dataReader.Close();
                                    if (i == 0)
                                    {
                                        DataRow result_row = StructureTable.NewRow();
                                        result_row["RobotNo"] = robotno;
                                        result_row["PartNo"] = row["PartNo"].ToString().Trim();
                                        float quantity = 0;
                                        quantity = float.Parse(row["Quantity"].ToString().Trim());
                                        result_row["Quantity"] = quantity;
                                        result_row["PType"] = row["PType"].ToString();
                                        StructureTable.Rows.Add(result_row);
                                    }
                                }
                                catch (Exception ex)
                                {
                                    if (masterConnection.State == ConnectionState.Open)
                                    {
                                        masterConnection.Close();
                                    }
                                    MessageBox.Show("Cannot calculate: " + ex.Message);
                                    //MessageBox.Show(ex.Message);
                                }
                            }
                        }
                        cmd_sql.Dispose();

                        currentTable.Rows.Clear();
                        currentTable = nextTable.Copy();
                        nextTable.Rows.Clear();
                    }

                    masterConnection.Close();

                    foreach (DataRow result_row in StructureTable.Rows)
                    {
                        if (result_row["PType"].ToString() != "F")
                        {
                            DataRow[] add_row = resultTable.Select("RobotNo='" + result_row["RobotNo"].ToString() + "'"
                                + " AND PartNo='" + result_row["PartNo"] + "'");
                            if (add_row.Length > 0)
                            {
                                foreach (DataRow r in add_row)
                                {
                                    r.BeginEdit();
                                    r["Quantity"] = (float)r["Quantity"] + (float)result_row["Quantity"];
                                    r.EndEdit();
                                }
                            }
                            else
                            {
                                DataRow n = resultTable.NewRow();
                                n["RobotNo"] = result_row["RobotNo"].ToString();
                                n["PartNo"] = result_row["PartNo"].ToString();
                                n["Quantity"] = (float)result_row["Quantity"];
                                n["PType"] = result_row["PType"].ToString();
                                resultTable.Rows.Add(n);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    if (masterConnection.State == ConnectionState.Open)
                    {
                        masterConnection.Close();
                    }
                    //MessageBox.Show("Cannot get Robot list");
                    MessageBox.Show("Cannot get structure 1: " + ex.Message);
                }
            }
            return resultTable;
        }        

        ////////////////////////////////////////
        /// Get PartNo Purchase
        ////////////////////////////////////////
        private string getPartNoPurchase(string partno)
        {
            string ret = null;
            try
            {
                masterConnection.Open();
                SqlCommand cmd_sql = masterConnection.CreateCommand();
                SqlDataReader dataReader;                
                cmd_sql.CommandText = "SELECT * FROM OPENQUERY(AS400_MPFLIB, 'SELECT ITHNBN AS PARTNO,"
                    + " CONCAT (ITSHBN, ITNAM2) AS PARTPURCHASE FROM MPIT00 WHERE ITHNBN=''" + partno + "''')";
                dataReader = cmd_sql.ExecuteReader();
                if (dataReader.Read())
                {
                    ret = dataReader["PARTPURCHASE"].ToString();
                }
                dataReader.Close();
                cmd_sql.Dispose();
                masterConnection.Close();
            }
            catch
            {
                if (masterConnection.State == ConnectionState.Open)
                {
                    masterConnection.Close();
                }
            }
            return ret;
        }


        private void buttonCalculation_Click(object sender, EventArgs e)
        {
            string robot_calculate = null;
            ComboboxItem item = (ComboboxItem)this.robotNo.SelectedItem;
            robot_calculate = item.Value.ToString();
            
            DataTable final_result_Table = new DataTable();
            final_result_Table.Columns.Add("RobotNo", typeof(string));
            final_result_Table.Columns.Add("PartNo", typeof(string));
            final_result_Table.Columns.Add("Quantity", typeof(float));
            final_result_Table.Columns.Add("PType", typeof(string));
            final_result_Table.Columns.Add("Final", typeof(int));

            DataTable final_sum_Table = new DataTable();
            final_sum_Table.Columns.Add("RobotNo", typeof(string));
            final_sum_Table.Columns.Add("PartNo", typeof(string));
            final_sum_Table.Columns.Add("Quantity", typeof(float));
            final_sum_Table.Columns.Add("PType", typeof(string));

            this.statusText.Text = "Start to calculation Kosei 1";
            DataTable kosei1 = RobotStructure(robot_calculate);
            foreach (DataRow kosei1_row in kosei1.Rows)
            {
                this.statusText.Text = "Add Final to Kosei 1";
                DataRow add_row = final_result_Table.NewRow();
                add_row["RobotNo"] = kosei1_row["RobotNo"];
                add_row["PartNo"] = kosei1_row["PartNo"];
                add_row["Quantity"] = kosei1_row["Quantity"];
                add_row["PType"] = kosei1_row["PType"];
                add_row["Final"] = 0;
                final_result_Table.Rows.Add(add_row);
            }

            int i = 0;
            DataRow[] continue_rows = final_result_Table.Select("Final=0");
            while (continue_rows.Length > 0)
            {
                i++;
                this.statusText.Text = i + " round for Part Purchase";
                foreach (DataRow final_row in continue_rows)
                {
                    string part_purchase = getPartNoPurchase(final_row["PartNo"].ToString());
                    if (string.IsNullOrEmpty(part_purchase))
                    {
                        final_row.BeginEdit();
                        final_row["Final"] = 1;
                        final_row.EndEdit();
                    }
                    else
                    {
                        DataTable temp_Table = RobotStructure(part_purchase);
                        if (temp_Table.Rows.Count > 0)
                        {
                            foreach (DataRow temp_row in temp_Table.Rows)
                            {
                                DataRow add_temp_row = final_result_Table.NewRow();
                                add_temp_row["RobotNo"] = final_row["RobotNo"].ToString();
                                add_temp_row["PartNo"] = temp_row["PartNo"].ToString();
                                add_temp_row["Quantity"] = float.Parse(final_row["Quantity"].ToString()) * float.Parse(temp_row["Quantity"].ToString());
                                add_temp_row["PType"] = temp_row["PType"].ToString();
                                add_temp_row["Final"] = 0;
                                final_result_Table.Rows.Add(add_temp_row);
                            }
                            final_result_Table.Rows.Remove(final_row);
                        }
                        else
                        {
                            final_row.BeginEdit();
                            final_row["Final"] = 1;
                            final_row.EndEdit();
                        }
                    }
                }
                continue_rows = null;
                continue_rows = final_result_Table.Select("Final=0");
            }

            //this.statusText.Text = "Sum final table";
            foreach (DataRow sum_row in final_result_Table.Rows)
            {
                DataRow[] edit_rows = final_sum_Table.Select("RobotNo='" + sum_row["RobotNo"].ToString() + "'"
                        + " AND PartNo='" + sum_row["PartNo"] + "'");
                if (edit_rows.Length > 0)
                {
                    foreach (DataRow r in edit_rows)
                    {
                        r.BeginEdit();
                        r["Quantity"] = (float)r["Quantity"] + (float)sum_row["Quantity"];
                        r.EndEdit();
                    }
                }
                else
                {
                    DataRow n = final_sum_Table.NewRow();
                    n["RobotNo"] = sum_row["RobotNo"].ToString();
                    n["PartNo"] = sum_row["PartNo"].ToString();
                    n["Quantity"] = (float)sum_row["Quantity"];
                    n["PType"] = sum_row["PType"].ToString();
                    final_sum_Table.Rows.Add(n);
                }
            }

            showData(final_sum_Table);
        }

        private void showData(DataTable structure)
        {
            //this.statusText.Text = "Show data";
            gridStructureCheck.DataSource = null;
            if (structure.Rows.Count > 0)
            {
                gridStructureCheck.DataSource = structure;
            }
            else
            {
                DataTable newTable = new DataTable();
                newTable.Columns.Add("RobotNo", typeof(string));
                newTable.Columns.Add("PartNo", typeof(string));
                newTable.Columns.Add("Quantity", typeof(int));
                newTable.Columns.Add("PType", typeof(string));                
                gridStructureCheck.DataSource = newTable;
            }
            StructureCheck.Columns[0].Caption = "Robot No";
            StructureCheck.Columns[1].Caption = "Part No";
            StructureCheck.Columns[2].Caption = "Quantity";
            StructureCheck.Columns[3].Caption = "Part Type";            
            StructureCheck.BestFitColumns();           
        }
    }
}
